package com.ustcinfo.study.hbase.r1.chenzhiyu;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 *
 * JDBC操作工具类
 * MySQL的test数据库
 * 包含一个查询方法
 * @author chen.zhiyu2
 * @email chen.zhiyu2@ustcinfo.com
 *
 */
public class ExtractData {
    //以下四个字符串分别存放JDBC驱动、数据库地址、用户名、密码
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://119.23.26.27:3306/test";
    static final String USER = "chenzy";
    static final String PASS = "Chen_123";

    /**
     * 查询数据
     * 数据库 test.transfer_task_to
     * 输入6个列名作参数 String类型
     * @param s1 列名1
     * @param s2 列名2
     * @param s3 列名3
     * @param s4 列名4
     * @param s5 列名5
     * @param s6 列名6
     * @return List<Map<String,String>>结构数据
     *
     */
    public static List<Map<String,String>> getData(String s1, String s2, String s3, String s4, String s5, String s6){
        Connection connection = null;
        Statement statement = null;
        try {
            //加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //连接数据库
            connection = DriverManager.getConnection(DB_URL, USER, PASS);
            statement = connection.createStatement();
            //拼接SQL语句 select
            String sql = "SELECT "+ s1+","+s2+","+s3+","+s4+","+s5+","+s6+" FROM test.transfer_task_to;";
            //查询
            ResultSet resultSet = statement.executeQuery(sql);
            //用来存放数据
            List<Map<String,String>> list = new ArrayList<>();
            while (resultSet.next()){
                //用一个map存放数据 对应数据库表格的一行
                Map<String,String> map = new HashMap<>();
                map.put(s1, resultSet.getString(s1));
                map.put(s2, resultSet.getString(s2));
                map.put(s3, resultSet.getString(s3));
                map.put(s4, resultSet.getString(s4));
                map.put(s5, resultSet.getString(s5));
                map.put(s6, resultSet.getString(s6));
                list.add(map);
            }
            return list;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e){
            e.printStackTrace();
        } catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }
}
